from xlrd import open_workbook
from fetchemApp.models import Student
from fetchemApp.models import Course
from django.core.validators import email_re
import sys
sys.stdout = sys.stderr
################### IMPORTANT UPDATE
# The function not returns the status
# 'INVALID FILE' for a non excel file
# 'INVALID FORMAT' if the first Row doesn't contain 'RollNo', 'Name' and 'StudentID'
# 'INVALID RECORD' if some block field is empty or the StudentID id not a valid email ID
# 'SUCCESS' if everything goes well
###################

def exvalidator(file):
    try:
        book = open_workbook(file)
    except:
        return 'INVALID FILE'
    sheet = book.sheet_by_index(0)
    if sheet.nrows < 1:
        return 'EMPTY FILE'
    for row_index in range(sheet.nrows):
        row_num  = row_index
        if row_num==0:
            if sheet.cell(row_index,0).value =='RollNo' and sheet.cell(row_index,1).value=='Name' and sheet.cell(row_index,2).value=='StudentID':
                continue
            else:
                return 'INVALID FORMAT'
        else:
            roll_num=sheet.cell(row_index,0).value
            name = sheet.cell(row_index,1).value
            s_eid = sheet.cell(row_index,2).value
            if (not roll_num) or (not name) or (not s_eid):
                return 'INVALID RECORD'
            else:
                if email_re.match(s_eid):
                    qq=0
                else:
                    return 'INVALID RECORD'
        return 'VALID FILE'

def exsheet(file):
    try:
        book = open_workbook(file)
    except:
        return 'INVALID FILE'
    sheet = book.sheet_by_index(0)
    dict = {}
    # for column_index in range(sheet.ncolumns):
    #    column_num = column_index
    for row_index in range(sheet.nrows):
        row_num  = row_index
        if row_num==0:
            if sheet.cell(row_index,0).value =='RollNo' and sheet.cell(row_index,1).value=='Name' and sheet.cell(row_index,2).value=='StudentID':
                continue
            else:
                return 'INVALID FORMAT'
        else:
            roll_num=sheet.cell(row_index,0).value
            if (type(roll_num)is float):
                roll_num = int(roll_num)
            name = sheet.cell(row_index,1).value
            s_eid = sheet.cell(row_index,2).value
            values = [name, roll_num]
            if (not roll_num) or (not name) or (not s_eid):
                return 'INVALID RECORD'
            else:
                if email_re.match(s_eid):
                    dict[s_eid] = values
                else:
                    return 'INVALID RECORD'
    return dict

def course_enroll(file_path , course):
    k= exsheet(file_path)
    if k=='INVALID FILE':
        print 'exiting...'
        return k
    if k=='INVALID FORMAT':
        print 'exiting...'
        return k
    if k=='INVALID RECORD':
        print 'exiting...'
        return k
    print k
    z=course.Students.all()
    if(len(z)==0):
        l=k.keys()
        flag=2
        x=Student.objects.all()
        for kk in l:
            if(len(x)==0):
                new = Student(Name = k[kk][0], Email = kk, RollNo = k[kk][1])
                new.save()
                course.Students.add(new)
            else:
                x=Student.objects.all()
                flag=0
                for m in x:
                    if (kk==str(m.Email)):
                        flag=1
                        break
                if flag==1:
                    flag=0
                    course.Students.add(m)
                elif flag==0:
                    flag=2
                    new = Student(Name = k[kk][0], Email = kk, RollNo = k[kk][1])
                    new.save()
                    course.Students.add(new)
    else:
        for b in z:
            if (k.has_key(b.Email)):
                del k[b.Email]
            else:
                course.Students.remove(b)
        x = Student.objects.all()
        for t in x:
            if (k.has_key(t.Email)):
                course.Students.add(t)
                del k[t.Email]
        g=k.keys()
        for a in g:
            new = Student(Name = k[a][0], Email = a, RollNo = k[a][1])
            new.save()
            course.Students.add(new)
    return 'SUCCESS'

def useStudentList(UploadedFileObject, CourseObject):
    try:
        from django.conf import settings
        import os
        filepath = os.path.join(settings.MEDIA_ROOT, str(CourseObject.id) + '.xls')
        with open(filepath, 'wb+') as destination:
            for chunk in UploadedFileObject.chunks():
                destination.write(chunk)
        result = course_enroll(filepath, CourseObject)
        os.remove(filepath)
    except OSError as e:
        print e.strerror
        pass
    return result

def checkExcelSheet(UploadedFileObject):
    try:
        from django.conf import settings
        import os
        import datetime
        epoch = datetime.datetime.utcfromtimestamp(0)
        delta = datetime.datetime.now() - epoch
        fileNum = int(delta.total_seconds()*1000)
        filepath = os.path.join(settings.MEDIA_ROOT, (str(fileNum) + '.xls'))
        with open(filepath, 'wb+') as destination:
            for chunk in UploadedFileObject.chunks():
                destination.write(chunk)
        result = exvalidator(filepath)
        os.remove(filepath)
        if(result=='INVALID FILE'):
            return 'INVALID'
        elif(result=='EMPTY FILE'):
            return 'EMPTY'
        elif(result=='INVALID FORMAT'):
            return 'WRONGFORMAT'
        elif(result=='INVALID RECORD'):
            return 'WRONGRECORD'
        else:
            return 'VALID'
    except OSError as e:
        return 'INVALID'
    except:
        return 'Invalid'